package com.is.control;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.is.dbconn.DBConnect;
import com.is.model.ParamCourse;

public class ParamCourseControl {
	
	public static boolean insertParamCourse(ParamCourse pc){
		boolean flag=false;
		DBConnect dbConnect = new DBConnect();
		String sql = "insert into param_course (courseName,courseNameEn,typeId) values(?,?,?)";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.setString(1, pc.getCourseName());
			dbConnect.setString(2, pc.getCourseNameEn());
			dbConnect.setInt(3, pc.getTypeId());
			if(dbConnect.executeUpdate()){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("insertParamCourse ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	public static ParamCourse getCourseById(int id){
		DBConnect dbConnect= new DBConnect();
		String sql = "select * from param_course where id ='"+id+"'";
		ResultSet rs = null;
		ParamCourse pCourse = null;
		dbConnect.prepareStatement(sql);
		try {
			rs = dbConnect.executeQuery();
			if(rs.next()){
				pCourse = new ParamCourse();
				pCourse.setCourseName(rs.getString("courseName"));
				pCourse.setCourseNameEn(rs.getString("courseNameEn"));
				pCourse.setTypeId(rs.getInt("typeId"));
			    pCourse.setId(id);
			}
			return pCourse;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getCourseById ERROR");
		}finally{
			dbConnect.close();
		}
		return null;
	}
	
	public static boolean updateParamCourse(ParamCourse pc,int id){
		boolean flag=false;
		DBConnect dbConnect = new DBConnect();
		String sql = "update param_course set courseName=?,courseNameEn=?,typeId=? where id=?";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.setString(1, pc.getCourseName());
			dbConnect.setString(2, pc.getCourseNameEn());
			dbConnect.setInt(3, pc.getTypeId());
			dbConnect.setInt(4, id);
			if(dbConnect.executeUpdate()){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updateParamCourse ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	
	public static boolean deleteParamCourseById(int id){
		boolean flag=false;
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from param_course  where id=?";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.setInt(1, id);
			if(dbConnect.executeUpdate()){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("deleteParamCourseById ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	
	/**
	 * 根据上课学期获取所有已有的课程，当学期缺省时为获取所有课程
	 * @param typeId
	 * @return
	 */
	public static List<ParamCourse> getAllCoursesByType(String typeId){
		DBConnect dbConnect = new DBConnect();
		List<ParamCourse> pcs = new ArrayList<ParamCourse>();
		String sql = (typeId==null||typeId.equals(""))? "select * from param_course ORDER BY typeId":"select * from param_course where typeId = '"+typeId+"'order by id" ;
		ResultSet rs= null;
		dbConnect.prepareStatement(sql);
		try {
			rs = dbConnect.executeQuery();
			while(rs.next()){
				ParamCourse pc = new ParamCourse();
				pc.setCourseName(rs.getString("courseName"));
				pc.setCourseNameEn(rs.getString("courseNameEn"));
				pc.setTypeId(rs.getInt("typeId"));
				pc.setId(rs.getInt("id"));
				pcs.add(pc);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("getAllCoursesByGrade ERROR");
		}finally{
			dbConnect.close();
		}
		return pcs;
	}

}
